﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace ManagementProject
{

    public partial class frmEmployee : Form
    {
        SqlCommand cmd;
        SqlConnection con;
        SqlDataAdapter da, da2;
        DataSet ds, ds2;
        SqlCommandBuilder cb, cb2;
        DataTable dt, dt2;
        DataView dv;
        public string mgrID;

        bool flag = false;
        bool flagSearch = false; //use for switch Search/Cancel
        bool changed = false;
        bool flagchange = false;

        public frmEmployee()
        {

            InitializeComponent();
            loadEmployee();
            cboTitleOfCoutersy.SelectedIndex = 0;
            cboSearch.SelectedIndex = 0;
            cboCountry.SelectedIndex = 0;
            txtmgrID.Text = mgrID;

        }
        void InitialDBEmployee()
        {
            try
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getEmployees";
                da = new SqlDataAdapter(cmd);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds, "HR.Employees");
             
            }
            catch (Exception ex)
            {
                lbStatus.Text = "Can not load database";
                Status.Start();
            }


        }

        #region MyCode: New();
        //Load gridView
        void loadEmployee()
        {
            try
            {
                InitialDBEmployee();
                dt = ds.Tables["HR.Employees"];
                dv = new DataView(dt);
                gvEmployee.DataSource = dt;
                Status.Start();
            }
            catch (Exception ex)
            {

                gvEmployee = null;
                lbStatus.Text = "Can not load database";
                Status.Start();
            }
        }
        //New
        void New()
        {
            flagchange = false;
            gvManagers.Visible = false;
            btnChoose.Visible = false;
            btnNew.Enabled = true;
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            txtID.Clear();
            txtLastName.Clear();
            txtFirstName.Clear();
            txtTitle.Clear();
            txtAddress.Clear();
            txtCity.Clear();
            txtRegion.Clear();
            txtmgrID.Clear();
            mTBPostalCode.Clear();
            txtSearch.Clear();
            cboTitleOfCoutersy.SelectedIndex = 0;
            cboSearch.SelectedIndex = 0;
            mskPhone.Clear();
            errorProvider1.Clear();
            dTPDOB.Value = (DateTime.Today);
            dTPHireDate.Value = (DateTime.Today);
            loadEmployee();

        }
        //add New Employee
        void addNewEmployee()
        {
            try
            {
                dt = ds.Tables["HR.Employees"];
                DataRow r = dt.NewRow();
                r["lastname"] = txtLastName.Text;
                r["firstname"] = txtFirstName.Text;
                r["title"] = txtTitle.Text;
                r["titleofcourtesy"] = cboTitleOfCoutersy.Text;
                r["address"] = txtAddress.Text;
                r["city"] = txtCity.Text;
                r["region"] = txtRegion.Text;
                r["postalcode"] = mTBPostalCode.Text;
                r["country"] = cboCountry.Text;
                r["phone"] = mskPhone.Text;
                r["birthdate"] = dTPDOB.Value.ToShortDateString();
                r["hiredate"] = dTPHireDate.Value.ToShortDateString();
                if (txtmgrID.Text != "")
                    r["mgrid"] = txtmgrID.Text;
                dt.Rows.Add(r);
                da.Update(dt);
                New();//load lai Gridview
                lbStatus.Text = "Add Successfully";
                Status.Start();
            }
            catch (Exception e)
            {
                MessageBox.Show("ERORR!" + e.Message);
                lbStatus.Text = "Add Failed";
                Status.Start();
            }
        }
        //Update
        void updateEmployee()
        {
            try
            {
                DialogResult result1 = MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo);
                if (result1 == DialogResult.Yes)
                {

                    if (gvEmployee.SelectedRows.Count > 0)
                    {
                        dt = ds.Tables["HR.Employees"];
                        DataGridViewRow rv = gvEmployee.SelectedRows[0];
                        DataRow[] r = dt.Select(string.Format("empid = '{0}'", rv.Cells["empid"].Value.ToString()));

                        try
                        {
                            r[0]["empid"] = this.txtID.Text;
                            r[0]["lastname"] = this.txtLastName.Text;
                            r[0]["firstname"] = this.txtFirstName.Text;
                            r[0]["title"] = this.txtTitle.Text;
                            r[0]["titleofcourtesy"] = this.cboTitleOfCoutersy.Text;
                            r[0]["birthdate"] = this.dTPDOB.Value.ToShortDateString();
                            r[0]["hiredate"] = this.dTPHireDate.Value.ToShortDateString();
                            r[0]["address"] = this.txtAddress.Text;
                            r[0]["city"] = this.txtCity.Text;
                            if (txtRegion.Text != "")
                                r[0]["region"] = this.txtRegion.Text;
                            else
                                r[0]["region"] = DBNull.Value;
                            if (mTBPostalCode.Text != "")
                                r[0]["postalcode"] = this.mTBPostalCode.Text;
                            else
                                r[0]["postalcode"] = DBNull.Value;
                            r[0]["country"] = this.cboCountry.Text;
                            r[0]["phone"] = this.mskPhone.Text;
                            if (txtmgrID.Text != "")
                                r[0]["mgrid"] = this.txtmgrID.Text;
                            else
                                r[0]["mgrid"] = DBNull.Value;
                            lbStatus.Text = "Update Successfully";
                            Status.Start();
                            changed = true;
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("ERORR!" + ex.Message);
                            lbStatus.Text = "Update Failed";
                            Status.Start();

                        }
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
        //Delete
        void deleteEmployee()
        {
            panel1.Visible = false;
            DialogResult result1 = MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo);
            if (result1 == DialogResult.Yes)
            {
                try
                {
                    if (gvEmployee.SelectedRows.Count > 0)
                    {


                        string mgr = "";
                        DataGridViewRow rv = gvEmployee.SelectedRows[0];
                        dv = new DataView(dt);
                        mgr = rv.Cells["empid"].Value.ToString();
                        dv.RowFilter = string.Format("empid = '{0}'", int.Parse(rv.Cells["empid"].Value.ToString()));
                        DataRow[] r = dt.Select(string.Format("mgrid = '{0}'", int.Parse(mgr)));
                        if (r.Length != 0)
                        {
                            r[0]["mgrID"] = DBNull.Value;
                        }
                        da.Update(dt);
                        dv.Delete(0);
                        da.Update(dt);

                        changed = true;
                        lbStatus.Text = "Delete Successfully";
                        Status.Start();

                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    lbStatus.Text = "Delete Failed";
                    Status.Start();

                }
                finally
                {
                    New();
                }
            }
        }
        //searchEmployee
        void searchEmployee()
        {
            try
            {
                dv = new DataView(dt);
                string searchMethod = cboSearch.Text;
                if (flagSearch == false)
                {

                    btnSearch.Text = "Cancel";
                    btnNew.Enabled = false;
                    btnAdd.Enabled = false;
                    flagSearch = true; //flag for switch to Cancel
                    switch (searchMethod)
                    {
                        case "by ID":
                            dv.RowFilter = string.Format("empid = '{0}'", txtSearch.Text);
                            gvEmployee.DataSource = dv;
                            break;
                        case "by name":
                            dv.RowFilter = string.Format("firstname + ' ' + lastname like '%{0}%'", txtSearch.Text);
                            gvEmployee.DataSource = dv;
                            break;
                        case "by title":
                            dv.RowFilter = string.Format("title like '%{0}%'", txtSearch.Text);
                            gvEmployee.DataSource = dv;
                            break;
                        case "by country":
                            dv.RowFilter = string.Format("country like '%{0}%'", txtSearch.Text);
                            gvEmployee.DataSource = dv;
                            break;
                        case "by mgrID":
                            dv.RowFilter = string.Format("mgrid = '{0}'", txtSearch.Text);
                            gvEmployee.DataSource = dv;
                            break;
                    }
                    lbStatus.Text = "Search Successfully";
                    Status.Start();
                }
                else
                {
                    flagSearch = false;
                    btnSearch.Text = "Search";
                    btnNew.Enabled = true;
                    btnAdd.Enabled = true;
                    loadEmployee();
                    lbStatus.Text = "Reloaded Database";
                    Status.Start();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                lbStatus.Text = "Search Failed";
                Status.Start();
            }
        }

        void SaveDB()
        {
            try
            {
                dt = ds.Tables["HR.Employees"];
                da.Update(dt);
                MessageBox.Show("Save Successfully");
                lbStatus.Text = "Save Successfully";
                Status.Start();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
                lbStatus.Text = "Save Failed";
                Status.Start();
            }
        }


        //Validation
        bool validateInput()
        {
            string firstname = txtFirstName.Text;
            string lastname = txtLastName.Text;
            string title = txtTitle.Text;
            string address = txtAddress.Text;
            string country = cboCountry.Text;
            string city = txtCity.Text;
            string postCode = mTBPostalCode.Text;
            bool error = false;
            if (cboCountry.SelectedIndex == 0)
            {
                errorProvider1.SetError(cboCountry, "Please choose country!");
                error = true;

            }
            if (cboTitleOfCoutersy.SelectedIndex == 0)
            {
                errorProvider1.SetError(cboTitleOfCoutersy, "Please select!");
                error = true;
            }
            if (firstname.Length == 0)
            {
                errorProvider1.SetError(txtFirstName, "Please enter your first name!");
                error = true;
            }

            if (lastname.Length == 0)
            {
                errorProvider1.SetError(txtLastName, "Please enter your last name!");
                error = true;
            }
            if (title.Length == 0)
            {
                errorProvider1.SetError(txtTitle, "Please enter your title!");
                error = true;
            }

            if (address.Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Please enter your address!");
                error = true;
            }

            if (city.Length == 0)
            {
                errorProvider1.SetError(txtCity, "Please enter your City!");
                error = true;
            }




            DateTime currDate = DateTime.Now;
            int currYear = currDate.Year;
            int currDay = currDate.Day;
            int currMonth = currDate.Month;
            DateTime dateOfBirth = dTPDOB.Value;
            int birthYear = dateOfBirth.Year;
            DateTime dateOfHire = dTPHireDate.Value;
            int HireYear = dateOfHire.Year;
            int HireMonth = dateOfHire.Month;
            int HireDay = dateOfHire.Day;
            if (HireYear - birthYear < 18)
            {
                errorProvider1.SetError(dTPDOB, "You must be greater than or equal to 18 for hiring");
                error = true;
            }
   

            if (mskPhone.MaskCompleted == false)
            {
                errorProvider1.SetError(mskPhone, "Please enter required digit!");
                error = true;
            }
           
            if (error == true)
            {
                return false;
            }
            else
                errorProvider1.Clear();
            return true;
        }
        #endregion

        #region Validation of Input
        private void txtLastName_KeyDown(object sender, KeyEventArgs e)
        {
            flag = false;
            if (e.KeyCode < Keys.D0 || e.KeyCode > Keys.D9)
            {
                if (e.KeyCode < Keys.NumPad0 || e.KeyCode > Keys.NumPad9)
                {
                    flag = true;
                }
            }
            if (Control.ModifierKeys == Keys.Shift)
            {
                flag = true;
            }
        }
        private void txtLastName_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (flag == false)
            {
                e.Handled = true;
            }

        }
        private void txtFirstName_KeyDown(object sender, KeyEventArgs e)
        {

        }
        private void txtFirstName_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (flag == false)
            {
                e.Handled = true;
            }
        }
        private void mTBPostalCode_KeyDown(object sender, KeyEventArgs e)
        {
            flag = false;
            if (e.KeyCode < Keys.D0 || e.KeyCode > Keys.D9)
            {
                if (e.KeyCode < Keys.NumPad0 || e.KeyCode > Keys.NumPad9)
                {
                    if (e.KeyCode != Keys.Back)
                        flag = true;
                }
            }
        }
        private void mTBPostalCode_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (flag == true)
            {
                e.Handled = true;
            }
        }
        private void txtSearch_KeyDown(object sender, KeyEventArgs e)
        {
            if (cboSearch.SelectedIndex == 0 || cboSearch.SelectedIndex == 1) //when User choose Search "by ID or by mgrID"
            {

                flag = false;
                if (e.KeyCode < Keys.D0 || e.KeyCode > Keys.D9)
                {
                    if (e.KeyCode < Keys.NumPad0 || e.KeyCode > Keys.NumPad9)
                    {
                        if (e.KeyCode != Keys.Back)
                            flag = true;
                    }
                }
            }
            else //when User choose Search "by other"
            {

                flag = false;
                if (e.KeyCode < Keys.D0 || e.KeyCode > Keys.D9)
                {
                    if (e.KeyCode < Keys.NumPad0 || e.KeyCode > Keys.NumPad9)
                    {
                        flag = false;
                    }
                }
            }
        }
        private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (flag == true)
            {
                e.Handled = true;
            }
        }
        #endregion

        #region Event
        private void btnAdd_Click(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            if (validateInput() == false)
                return;
            addNewEmployee();


        }
        private void btnNew_Click(object sender, EventArgs e)
        {
            New();

        }
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtSearch.Text != "")
            {
                searchEmployee();
            }
        }
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            panel1.Visible = false;
            errorProvider1.Clear();
            if (validateInput())
            {
                updateEmployee();
                btnSave.Enabled = true;
            }
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            deleteEmployee();
            btnSave.Enabled = true;


        }
        private void gvEmployee_SelectionChanged(object sender, EventArgs e)
        {

            try
            {
                if (gvEmployee.SelectedRows.Count > 0)
                {
                    btnUpdate.Enabled = true;
                    btnDelete.Enabled = true;
                    btnAdd.Enabled = false;
                    flagchange = true;
                    DataGridViewRow r = gvEmployee.SelectedRows[0];
                    txtID.Text = r.Cells[0].Value.ToString();
                    txtFirstName.Text = r.Cells[2].Value.ToString();
                    txtLastName.Text = r.Cells[1].Value.ToString();
                    txtTitle.Text = r.Cells[3].Value.ToString();
                    cboTitleOfCoutersy.Text = r.Cells[4].Value.ToString();
                    string s = r.Cells[5].Value.ToString();
                    for (int i = 0; i < 5; i++)
                    {
                        string a = (string)cboTitleOfCoutersy.Items[i];
                        if (s.Equals(a))
                        {
                            cboTitleOfCoutersy.SelectedIndex = i;
                            break;
                        }
                    }
                    DateTime dt1, dt2;
                    DateTime.TryParse(r.Cells[5].Value.ToString(), out dt1);
                    dTPDOB.Value = dt1;
                    DateTime.TryParse(r.Cells[6].Value.ToString(), out dt2);
                    dTPHireDate.Value = dt2;
                    txtAddress.Text = r.Cells[7].Value.ToString();
                    txtCity.Text = r.Cells[8].Value.ToString();
                    txtRegion.Text = r.Cells[9].Value.ToString();
                    mTBPostalCode.Text = r.Cells[10].Value.ToString();
                    cboCountry.Text = r.Cells[11].Value.ToString();
                    mskPhone.Text = r.Cells[12].Value.ToString();
                    txtmgrID.Text = r.Cells[13].Value.ToString();
                }
            }
            catch
            {
                lbStatus.Text = "Can not select";
            }
        }
        private void btnSave_Click(object sender, EventArgs e)
        {
            SaveDB();
            changed = false;
            btnSave.Enabled = false;
        }
        private void FrmEmployee_Load(object sender, EventArgs e)
        {
            loadEmployee();
        }
        private void cboSearch_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtSearch.Clear();
        }
        private void frmEmployee_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult result1;
            if (changed)
            {

                result1 = MessageBox.Show("Do you want to save changes?", "Not save yet!!!", MessageBoxButtons.YesNoCancel);
                if (result1 == DialogResult.Cancel)
                {
                    e.Cancel = true;
                }
                else if (result1 == DialogResult.Yes)
                {
                    SaveDB();
                    changed = false;
                }
            }

        }
        private void gvManagers_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                if (gvManagers.SelectedRows.Count > 0)
                {

                    DataGridViewRow r = gvManagers.SelectedRows[0];
                    txtmgrID.Text = r.Cells[0].Value.ToString();
                    if (txtmgrID.Text.Equals(txtID.Text))
                    {
                        btnChoose.Enabled = false;
                        errorProvider1.SetError(btnChoose, "Employees can not manage themselves");
                    }
                    else
                    {
                        btnChoose.Enabled = true;
                        errorProvider1.Clear();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void btnChoose_Click(object sender, EventArgs e)
        {
            if (!flagchange)
            {

                btnAdd.Enabled = true;
                btnNew.Enabled = true;
            }
            else
            {
                btnUpdate.Enabled = true;
                btnNew.Enabled = true;
                btnDelete.Enabled = true;
            }
            panel1.Visible = false;
            btnChoose.Visible = false;
            gvManagers.Visible = false;
        }
        private void Status_Tick(object sender, EventArgs e)
        {
            lbStatus.Text = null;
        }
        private void btnMgrID_Click(object sender, EventArgs e)
        {
            try
            {
                btnAdd.Enabled = false;
                btnNew.Enabled = false;
                btnSave.Enabled = false;
                panel1.Visible = true;
                gvManagers.Visible = true;
                btnChoose.Visible = true;
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
                dv = new DataView(dt);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getManagers";
                da2 = new SqlDataAdapter(cmd);
                cb2 = new SqlCommandBuilder(da2);
                ds2 = new DataSet();
                da2.FillSchema(ds2, SchemaType.Mapped);
                da2.Fill(ds2, "HR.Employees");
                gvManagers.DataSource = dv;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            try
            {
                dt2 = ds2.Tables["HR.Employees"];
                dv = new DataView(dt2);
                gvManagers.DataSource = dt2;
            }
            catch (Exception ex)
            {

                MessageBox.Show("ERROR!!!" + ex.Message);
                gvManagers = null;
            }

        }
        #endregion

        private void button1_Click(object sender, EventArgs e)
        {

            if (!flagchange)
            {

                btnAdd.Enabled = true;
                btnNew.Enabled = true;
            }
            else
            {
                btnUpdate.Enabled = true;
                btnNew.Enabled = true;
                btnDelete.Enabled = true;
            }
            txtmgrID.Text = "";
            panel1.Visible = false;
        }
    }

}

